﻿// -----------------------------------------------------------------------
// <copyright file="RefColumn.cs" company="JSC Centido">
//  Copyright (c) JSC Centido. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

using System.Data.Odbc;

namespace Centido.Core
{
    using System;
    using System.Collections.Generic;


    /// <summary>
    ///   A reference table column.
    /// </summary>
    /// <remarks>A reference column can point to many tables.
    /// A reference target is created for each of those tables and stores
    /// additional information such as the need for cascade delete, the
    /// table back display name etc. The column is allowed to contain
    /// only the value that correspond to the ids of the rows in the tables
    /// that are listed as reference targets. The column can have a default value.</remarks>
    /// <example>
    ///   The following example shows how to use a reference column.
    /// <code>
    ///     using System;
    ///     using System.Data.Common;
    ///     using Centido.Core;
    /// 
    ///     class Sample
    ///     {
    ///         static void Main()
    ///         {
    ///             var domainConfig = new DomainConfiguration()
    ///             {
    ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
    ///                 DbProviderName = "MSSQLSERVER"
    ///             };
    ///         
    ///             var domain = Domain.Build(domainConfig);
    ///         
    ///             domain.DeleteAll();
    ///             
    ///             //Adding two tables
    ///             var firstTable = domain.AddTable("first_table");
    ///             var secondTable = domain.AddTable("second_table");
    /// 
    ///             firstTable.GrantAll();
    ///             secondTable.GrantAll();
    ///             
    ///             //Creating a reference column
    ///             var column = firstTable.AddColumn("ref_column", ColumnType.Reference)
    ///                 as RefColumn;
    /// 
    ///             //Adding reference targets
    ///             column.AddTarget(firstTable).HasCascadeDelete = true; //This is a self-reference
    ///             column.AddTarget(secondTable).HasCascadeDelete = true;  
    /// 
    ///             //Inserting some rows
    ///             var secondTableRowId = secondTable.InsertRow();
    ///             var firstTableRowId = firstTable.InsertRow(column.Value(secondTableRowId));
    ///             firstTable.InsertRow(column.Value(firstTableRowId)); //This is a self-reference
    /// 
    ///             //Trying to insert an invalid value
    ///             try
    ///             {
    ///                 firstTable.InsertRow(column.Value(Guid.NewGuid()));
    ///             }
    ///             catch(DbException)
    ///             {
    ///                 Console.WriteLine("Inserting an invalid value is forbidden");
    ///             }
    /// 
    ///             Console.WriteLine("The first table: row count = {0}", firstTable.RowCount);
    ///             Console.WriteLine("The second table: row count = {0}", secondTable.RowCount);
    /// 
    ///             Console.WriteLine("Deleting the first row in the chain of rows");
    /// 
    ///             secondTable.DeleteRow(secondTableRowId);
    /// 
    ///             Console.WriteLine("The first table: row count = {0}", firstTable.RowCount);
    ///             Console.WriteLine("The second table: row count = {0}", secondTable.RowCount);
    ///         }
    ///     }
    /// </code>
    /// </example>
    public sealed class RefColumn : Column, IDefaultable<Guid?>
    {
        /// <summary>
        ///   The default value of the column.
        /// </summary>
        private Guid? _defaultValue;

        /// <summary>
        ///   Initializes a new instance of the <see cref = "Centido.Core.RefColumn" /> class
        ///   for the specified column id and parent table.
        /// </summary>
        /// <param name = "columnId">The column id.</param>
        /// <param name = "table">The parent table.</param>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="table"/> is null.</exception>
        internal RefColumn(Guid columnId, Table table)
            : base(columnId, table)
        {
        }

        /// <summary>
        ///   Initializes a new instance of the <see cref = "Centido.Core.RefColumn" /> class
        ///   for the specified column id, name, display name,
        /// description, nullability, order index, customization level and parent table.
        /// </summary>
        /// <param name="columnId">The column id.</param>
        /// <param name="table">The parent table.</param>
        /// <param name="columnName">The column name.</param>
        /// <param name="displayName">The column display name.</param>
        /// <param name="description">The column description.</param>
        /// <param name="isNotNull">The column ability to hold null values.</param>
        /// <param name="orderIndex">The column order index.</param>
        /// <param name="customizationlevel">The column customization level.</param>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="table"/> is null.</exception>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="columnName"/> is null.</exception>
        /// <exception cref="ArgumentException">Thrown when the value of <paramref name="columnName"/> is an empty string.</exception>
        internal RefColumn(Guid columnId, Table table,
            string columnName, string displayName,
            string description, bool isNotNull,
            int orderIndex, int customizationlevel) : base(columnId, table,
            columnName, displayName, description, isNotNull, orderIndex, customizationlevel)
        {
        }

        /// <summary>
        ///   Gets the column type.
        /// </summary>
        public override ColumnType ColumnType
        {
            get { return ColumnType.Reference; }
        }

        /// <summary>
        ///   Gets the column value database type.
        /// </summary>
        public override OdbcType ValueOdbcType
        {
            get { return OdbcType.Binary; }
        }

        /// <summary>
        ///   Gets the reference targets of the column.
        /// </summary>
        /// <example>
        ///   The following example shows how to enumerate the reference column targets.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Adding three tables
        ///             var firstTable = domain.AddTable("first_table");
        ///             var secondTable = domain.AddTable("second_table");
        ///             var thirdTable = domain.AddTable("third_table");
        ///             
        ///             //Creating a reference column
        ///             var column = firstTable.AddColumn("ref_column", ColumnType.Reference)
        ///                 as RefColumn;
        /// 
        ///             //Adding reference targets
        ///             column.AddTarget(firstTable);
        ///             column.AddTarget(secondTable);  
        ///             column.AddTarget(thirdTable);
        /// 
        ///             //Enumerating reference targets
        ///             foreach(var t in column.Targets)
        ///                 Console.WriteLine("Reference target: table = {0}", t.RefTable.Name);
        ///         }
        ///     }   
        /// </code>
        /// </example>
        public ICollection<RefTarget> Targets
        {
            get
            {
                var resultList = new List<RefTarget>();

                Domain.DbAccess.ExecuteReader(
                    cmd =>
                    {
                        cmd.CommandText = string.Format(
                            "select {0} from {1} where {2}=?",
                            Db.Tables.ReferenceTargetList.Col.ReferencedTableId,
                            Domain.GetObjectFullName(Db.Tables.ReferenceTargetList.Name),
                            Db.Tables.ReferenceTargetList.Col.ColumnId);

                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("column_id", OdbcType.Binary, GetRawId()));
                    },
                    reader =>
                    {
                        var refTableId = reader.GetGuid(Db.Tables.ReferenceTargetList.Col.ReferencedTableId);

                        if (refTableId != null)
                            resultList.Add(new RefTarget(this, new Table(refTableId.Value, Domain)));
                    });

                return resultList;
            }
        }

        /// <summary>
        ///   Gets the backend table name.
        /// </summary>
        protected override string BackEndTableName
        {
            get { return Domain.GetObjectFullName(Db.Tables.ReferenceColumnList.Name); }
        }

        /// <summary>
        ///   Gets or sets the default value of the column.
        /// </summary>
        /// <remarks>
        /// <para>The default value needs to be a valid value that is
        /// it should correspond to a id of a row in a table that is marked as a reference target.</para>
        /// <para>The current user has to have the permission to change the schema in order to set this property.</para></remarks>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user has no permission to set this property.</exception>
        /// <example>
        ///   The following example shows how to set the default value of a reference column.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Adding two tables
        ///             var firstTable = domain.AddTable("first_table");
        ///             var secondTable = domain.AddTable("second_table");
        ///             firstTable.GrantAll();
        ///             secondTable.GrantAll();
        ///             
        ///             //Creating a reference column
        ///             var column = firstTable.AddColumn("ref_column", ColumnType.Reference)
        ///                 as RefColumn;
        /// 
        ///             //Adding a reference target that points to the second table
        ///             column.AddTarget(secondTable);
        ///         
        ///             //Creating a row in the second table
        ///             var secondTableRowId = secondTable.InsertRow();
        /// 
        ///             //Setting the default column value
        ///             column.DefaultValue = secondTableRowId;
        /// 
        ///             //Inserting a row in the first table
        ///             var firstTableRowId = firstTable.InsertRow();
        /// 
        ///             Console.WriteLine("Reference column: value = {0}",
        ///                 column.Read(firstTableRowId).GetGuid());
        ///         }
        ///     }   
        /// </code>
        /// </example>
        public Guid? DefaultValue
        {
            get { return _defaultValue ?? (_defaultValue = DbValue.GetGuid(
                ReadProperty(Db.Tables.ReferenceColumnList.Col.DefaultValue))); }
            set
            {
                if (_defaultValue.HasValue && _defaultValue == value) return;

                var byteValue = value != null ? value.Value.ToByteArray() : null;

                Domain.DbAccess.ExecuteNonQuery(
                    cmd =>
                    {
                        cmd.CommandText = string.Format("{{call {0}(?,?)}}",
                            Domain.GetObjectFullName(Db.Procedures.SetReferenceColumnDefaultValue));

                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_column_id",
                            OdbcType.Binary, GetRawId()));
                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_default_value",
                            OdbcType.Binary, DbValue.GetDbValue(byteValue)));
                    });

                _defaultValue = value;
            }
        }

        /// <summary>
        ///   Enables of disables the trigger that checks reference values in the column.
        /// </summary>
        /// <remarks>
        /// <para>By default the trigger is enabled and reference values are checked.
        /// Disabling reference checking is useful when trying to insert large volumes of data
        /// as quickly as possible or when loading hierarchical data.
        /// </para>
        /// <para>
        /// The current user has to have the permission to change the schema in order to set this property.
        /// </para></remarks>
        /// <value>True, if the trigger is enabled; otherwise, false.</value>
        /// <example>
        ///   The following example shows how to disable reference checking.
        /// <code>
        ///     using System;
        ///     using System.Data.Common;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Creating two tables
        ///             var firstTable = domain.AddTable("first_table");
        ///             var secondTable = domain.AddTable("second_table");
        /// 
        ///             firstTable.GrantAll();
        ///             secondTable.GrantAll();
        /// 
        ///             //Creating a reference column
        ///             var refColumn = RefColumn.Add(firstTable, "ref_col");
        /// 
        ///             refColumn.AddTarget(secondTable);
        /// 
        ///             // Trying to insert invalid value
        ///             try
        ///             {
        ///                 firstTable.InsertRow(refColumn.Value(Guid.NewGuid()));
        ///             }
        ///             catch(DbException)
        ///             {
        ///                 Console.WriteLine("Inserting a invalid reference value is forbidden");
        ///             }
        /// 
        ///             Console.WriteLine("Disabling the check trigger");
        /// 
        ///             refColumn.HasReferenceChecking = false;
        /// 
        ///             // Inserting an invalid value
        ///             firstTable.InsertRow(refColumn.Value(Guid.NewGuid()));
        /// 
        ///             if (firstTable.RowCount == 1)
        ///                 Console.WriteLine("An invalid reference value was inserted");
        ///         }
        ///     }
        /// </code>  
        /// </example>
        public bool HasReferenceChecking
        {
            get { return DbValue.GetBoolean(ReadProperty(Db.Tables.ReferenceColumnList.Col.HasReferenceChecking)).GetValueOrDefault(); }
            set 
            {
                Domain.DbAccess.ExecuteNonQuery(cmd =>
                    {
                        cmd.CommandText = string.Format("{{call {0}(?,?)}}",
                            Domain.GetObjectFullName(Db.Procedures.SetReferenceChecking));

                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_column_id",
                            OdbcType.Binary, GetRawId()));
                        cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_ref_checking",
                            OdbcType.Decimal, value ? 1 : 0));
                    });
            }
        }

        /// <summary>
        ///   Retrives a reference target that points to the specified table.
        /// </summary>
        /// <param name = "referencedTable">The specified table.</param>
        /// <returns>Returns a reference target that points to the specified table if the one exists;
        /// otherwise, returns null.</returns>
        /// <example>
        ///   The following example shows how to get a reference target by the
        /// table it points to.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Adding two tables
        ///             var firstTable = domain.AddTable("first_table");
        ///             var secondTable = domain.AddTable("second_table");
        ///             firstTable.GrantAll();
        ///             secondTable.GrantAll();
        ///             
        ///             //Creating a reference column
        ///             var column = firstTable.AddColumn("ref_column", ColumnType.Reference)
        ///                 as RefColumn;
        /// 
        ///             //Adding a reference target that points to the second table
        ///             column.AddTarget(secondTable).BackDisplayName = "The reference from the first table to the second table";
        /// 
        ///             //Adding a reference target that points to the first table
        ///             column.AddTarget(firstTable).BackDisplayName = "The reference from the first table to itself.";
        /// 
        ///             Console.WriteLine("Target: {0}", column.GetTarget(firstTable).BackDisplayName);
        ///             Console.WriteLine("Target: {0}", column.GetTarget(secondTable).BackDisplayName);
        ///         }
        ///     }   
        /// </code>
        /// </example>
        public RefTarget GetTarget(Table referencedTable)
        {
            if (referencedTable == null)
                throw new ArgumentNullException("referencedTable");

            RefTarget result = null;

            Domain.DbAccess.ExecuteReader(
                cmd =>
                {
                    cmd.CommandText = string.Format(
                        "select 1 from {0} where {1}=? and {2}=?",
                        Domain.GetObjectFullName(Db.Tables.ReferenceTargetList.Name),
                        Db.Tables.ReferenceTargetList.Col.ColumnId,
                        Db.Tables.ReferenceTargetList.Col.ReferencedTableId);

                    cmd.Parameters.Add(Domain.DbAccess.CreateParameter("column_id", OdbcType.Binary, GetRawId()));
                    cmd.Parameters.Add(
                        Domain.DbAccess.CreateParameter("ref_table_id", OdbcType.Binary, referencedTable.GetRawId()));
                },
                reader => { result = new RefTarget(this, referencedTable); });

            return result;
        }

        /// <summary>
        ///   Adds a new reference target.
        /// </summary>
        /// <remarks>
        /// <para>A reference target can point to the same table that contains
        /// the refenence column itself.</para>
        /// <para>The current user has to have the permission to change the schema in order to execute this function.</para>
        /// </remarks>
        /// <param name = "referencedTable">The referenced table of the new target.</param>
        /// <returns>Returns the newly created reference target.</returns>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="referencedTable"/> is null.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user has no permission to execute this function.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when there is already a reference target that points to the same table.</exception>
        /// <example>
        ///   The following example shows how to add reference targets.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Adding two tables
        ///             var firstTable = domain.AddTable("first_table");
        ///             var secondTable = domain.AddTable("second_table");
        ///             firstTable.GrantAll();
        ///             secondTable.GrantAll();
        ///             
        ///             //Creating a reference column
        ///             var column = firstTable.AddColumn("ref_column", ColumnType.Reference)
        ///                 as RefColumn;
        /// 
        ///             //Adding a reference target that points to the second table
        ///             column.AddTarget(secondTable).HasCascadeDelete = true;
        /// 
        ///             //Adding a reference target that points to the first table
        ///             column.AddTarget(firstTable).HasCascadeDelete = true;
        /// 
        ///             //Inserting a row into the second table
        ///             var secondTableRowId = secondTable.InsertRow();
        /// 
        ///             //Inserting a row into the first table
        ///             var firstTableRowId = firstTable.InsertRow(column.Value(secondTableRowId));
        /// 
        ///             //Inserting another row that references the previous one
        ///             firstTable.InsertRow(column.Value(firstTableRowId));
        /// 
        ///             Console.WriteLine("The first table: row count = {0}", firstTable.RowCount);
        ///             Console.WriteLine("The second table: row count = {0}", secondTable.RowCount);
        /// 
        ///             //Deleting the last row
        ///             Console.WriteLine("Deleting the first row");
        /// 
        ///             secondTable.DeleteRow(secondTableRowId);
        ///             
        ///             Console.WriteLine("The first table: row count = {0}", firstTable.RowCount);
        ///             Console.WriteLine("The second table: row count = {0}", secondTable.RowCount);
        ///         }
        ///     }   
        /// </code>
        /// </example>
        public RefTarget AddTarget(Table referencedTable)
        {
            if (referencedTable == null)
                throw new ArgumentNullException("referencedTable");

            Domain.DbAccess.ExecuteNonQuery(
                cmd =>
                {
                    cmd.CommandText = string.Format("{{call {0}(?,?)}}",
                        Domain.GetObjectFullName(Db.Procedures.AddReferenceTarget));

                    cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_column_id",
                        OdbcType.Binary, GetRawId()));
                    cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_ref_table_id",
                        OdbcType.Binary, referencedTable.GetRawId()));
                });

            return new RefTarget(this, referencedTable);
        }

        /// <summary>
        ///   Deletes the specified reference target.
        /// </summary>
        /// <remarks>
        /// <para>Deleting a reference target results in setting the column to null for those rows that contain
        /// references on the target to be deleted.</para>
        /// <para>The currrent user has to have the permission to change the schema in order to execute this function.</para>
        /// </remarks>
        /// <param name = "refTarget">The specified reference target.</param>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="refTarget"/> is null.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when there is no such reference target.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when the column is not nullable and there are rows that contain
        /// references on the target to be deleted.</exception>
        /// <example>
        ///   The following example shows how to delete a reference target.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Adding two tables
        ///             var firstTable = domain.AddTable("first_table");
        ///             var secondTable = domain.AddTable("second_table");
        ///             firstTable.GrantAll();
        ///             secondTable.GrantAll();
        ///             
        ///             //Creating a reference column
        ///             var column = firstTable.AddColumn("ref_column", ColumnType.Reference)
        ///                 as RefColumn;
        /// 
        ///             //Adding a reference target
        ///             var target = column.AddTarget(secondTable);
        /// 
        ///             //Inserting a row into the second table
        ///             var secondTableRowId = secondTable.InsertRow();
        /// 
        ///             //Inserting a row into the first table
        ///             var firstTableRowId = firstTable.InsertRow(column.Value(secondTableRowId));
        /// 
        ///             //Reading the reference
        ///             Console.WriteLine("Reference: value = {0}", column.Read(firstTableRowId).GetGuid());
        /// 
        ///             //Deleting the reference target
        ///             Console.WriteLine("Deleting the reference target");
        /// 
        ///             column.DeleteTarget(target);
        /// 
        ///             //Reading the reference
        ///             Console.WriteLine("Reference: value = {0}", column.Read(firstTableRowId).GetGuid());
        ///         }
        ///     }
        /// </code>
        /// </example>
        public void DeleteTarget(RefTarget refTarget)
        {
            if (refTarget == null)
                throw new ArgumentNullException("refTarget");

            DeleteTarget(refTarget.RefTable);
        }

        /// <summary>
        ///   Deletes a reference target to the specified table.
        /// </summary>
        /// <remarks>
        /// <para>Deleting a reference target results in setting the column to null for those rows that contain
        /// references on the target to be deleted.</para>
        /// <para>The currrent user has to have the permission to change the schema in order to execute this function.</para>
        /// </remarks>
        /// <param name = "referencedTable">The specified table.</param>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="referencedTable"/> is null.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when the current user has no permission to execute this function.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when there is no such reference target.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when the column is not nullable and there are rows that contain
        /// references on the target to be deleted.</exception>
        /// <example>
        ///   The following example shows how to delete a reference target.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Adding two tables
        ///             var firstTable = domain.AddTable("first_table");
        ///             var secondTable = domain.AddTable("second_table");
        ///             firstTable.GrantAll();
        ///             secondTable.GrantAll();
        ///             
        ///             //Creating a reference column
        ///             var column = firstTable.AddColumn("ref_column", ColumnType.Reference)
        ///                 as RefColumn;
        /// 
        ///             //Adding a reference target
        ///             column.AddTarget(secondTable);
        /// 
        ///             //Inserting a row into the second table
        ///             var secondTableRowId = secondTable.InsertRow();
        /// 
        ///             //Inserting a row into the first table
        ///             var firstTableRowId = firstTable.InsertRow(column.Value(secondTableRowId));
        /// 
        ///             //Reading the reference
        ///             Console.WriteLine("Reference: value = {0}", column.Read(firstTableRowId).GetGuid());
        /// 
        ///             //Deleting the reference target
        ///             Console.WriteLine("Deleting the reference target");
        /// 
        ///             column.DeleteTarget(secondTable);
        /// 
        ///             //Reading the reference
        ///             Console.WriteLine("Reference: value = {0}", column.Read(firstTableRowId).GetGuid());
        ///         }
        ///     }
        /// </code>
        /// </example>
        public void DeleteTarget(Table referencedTable)
        {
            if (referencedTable == null)
                throw new ArgumentNullException("referencedTable");

            Domain.DbAccess.ExecuteNonQuery(
                cmd =>
                {
                    cmd.CommandText = string.Format("{{call {0}(?,?)}}",
                        Domain.GetObjectFullName(Db.Procedures.DeleteReferenceTarget));

                    cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_column_id",
                        OdbcType.Binary, GetRawId()));
                    cmd.Parameters.Add(Domain.DbAccess.CreateParameter("a_ref_table_id",
                        OdbcType.Binary, referencedTable.GetRawId()));
                });
        }

        /// <summary>
        ///   Creates a column value.
        /// </summary>
        /// <remarks>This method is used when inserting table rows.</remarks>
        /// <param name = "columnValue">The value of the column.</param>
        /// <returns>Returns the column value.</returns>
        /// <example>
        ///   The following example shows how to insert into a reference column.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///         
        ///             domain.DeleteAll();
        ///             
        ///             //Adding two tables
        ///             var firstTable = domain.AddTable("first_table");
        ///             var secondTable = domain.AddTable("second_table");
        ///             firstTable.GrantAll();
        ///             secondTable.GrantAll();
        ///             
        ///             //Creating a reference column
        ///             var column = firstTable.AddColumn("ref_column", ColumnType.Reference)
        ///                 as RefColumn;
        /// 
        ///             //Adding a reference target
        ///             column.AddTarget(secondTable);
        /// 
        ///             //Inserting a row into the second table
        ///             var id = secondTable.InsertRow();
        /// 
        ///             //Inserting a row into the first table
        ///             firstTable.InsertRow(column.Value(id)); 
        ///         }
        ///     }   
        /// </code>
        /// </example>
        public override ColumnValue Value(object columnValue)
        {
            if (columnValue is Guid)
                return new ColumnValue(this, ((Guid)columnValue).ToByteArray());

            return new ColumnValue(this, columnValue);
        }

        /// <summary>
        ///   Prepares the column for removing.
        /// </summary>
        protected override void PrepareForRemoving()
        {
            if (IsNotNull)
                IsNotNull = false;

            // Ref targets will be removed on cascade delete
            // If ref targets are removed manually - then a strange bug appears
        }

        /// <summary>
        ///  Refreshes the properties of the column.
        /// </summary>
        public override void Refresh()
        {
            base.Refresh();

            _defaultValue = null;
        }

        /// <summary>
        ///   Adds a new reference column with the specified name to the specified table.
        /// </summary>
        /// <remarks>The current user has to have the permission to change the schema in order to execute this function.</remarks>
        /// <param name="table">The table.</param>
        /// <param name="columnName">The column name.</param>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="table"/> is null.</exception>
        /// <exception cref="System.ArgumentNullException">Thrown when the value of <paramref name="columnName"/> is null.</exception>
        /// <exception cref="System.ArgumentException">Thrown when the value of <paramref name="columnName"/> is an empty string.</exception>
        /// <exception cref="System.ArgumentException">Thrown when the value of <paramref name="columnName"/> is not a valid identifier.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown the current user has no permission to execute this function.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when there is a column with the same name in the same user-defined table.</exception>
        /// <returns>Returns the newly created reference table column.</returns>
        /// <example>
        ///   The following example shows how to add a reference column.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///             
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a new table
        ///             var table = domain.AddTable("my_table");
        /// 
        ///             //Creating a reference table column
        ///             var column = RefColumn.Add(table, "my_column");
        ///         }
        ///     }
        /// </code>
        /// </example>
        public static RefColumn Add(Table table, string columnName)
        {
            if (table == null)
                throw new ArgumentNullException("table");

            return table.AddColumn(columnName, ColumnType.Reference) as RefColumn;
        }


        /// <summary>
        ///   Adds a new reference table column with the specified id and name to the specified table.
        /// </summary>
        /// <remarks>The current user has to have the permission to change the schema in order to execute this function.</remarks>
        /// <param name="table">The table.</param>
        /// <param name="columnId">The column id.</param>
        /// <param name="columnName">The column name.</param>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="table"/> is null.</exception>
        /// <exception cref="System.ArgumentNullException">Thrown when the value of <paramref name="columnName"/> is null.</exception>
        /// <exception cref="System.ArgumentException">Thrown when the value of <paramref name="columnName"/> is an empty string.</exception>
        /// <exception cref="System.ArgumentException">Thrown when the value of <paramref name="columnName"/> is not a valid identifier.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown the current user has no permission to execute this function.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when there is a column with the same id or the same name in the same user-defined table.</exception>
        /// <returns>Returns the newly created reference table column.</returns>
        /// <example>
        ///   The following example shows how to create a reference table column.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///             
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a new table
        ///             var table = domain.AddTable("my_table");
        /// 
        ///             //Creating a reference table column
        ///             var column = RefColumn.Add(table, Guid.NewGuid(), "my_column");
        ///         }
        ///     }
        /// </code>
        /// </example>
        public static RefColumn Add(Table table, Guid columnId, string columnName)
        {
            if (table == null)
                throw new ArgumentNullException("table");

            return table.AddColumn(columnId, columnName, ColumnType.Reference) as RefColumn;
        }


        /// <summary>
        ///   Ensures that the table contains a reference column with the specified name.
        /// </summary>
        /// <remarks>The current user has to have the permission to change the schema in order to execute this function.</remarks>
        /// <param name="table">The table.</param>
        /// <param name="columnName">The column name.</param>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="table"/> is null.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when trying to add a column and
        /// the current user has no permission to change the schema.</exception>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="columnName"/> is null.</exception>
        /// <exception cref="ArgumentException">Thrown when the value of <paramref name="columnName"/> is an empty string.</exception>
        /// <exception cref="ArgumentException">Thrown when the value of <paramref name="columnName"/> is not a valid identifier.</exception>
        /// <exception cref="DbOperationException">Thrown when the table already has a column with the 
        /// specified name but different column type.</exception>
        /// <returns>Returns a reference table column with the specified name.</returns>
        /// <example>
        ///   The following example shows how to ensure that a table has a reference column with the specified name.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///             
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a new table
        ///             var table = domain.AddTable("my_table");
        /// 
        ///             //Creating a reference table column
        ///             table.AddColumn("first_column", ColumnType.Reference);
        /// 
        ///             //Ensuring the first column
        ///             RefColumn.Ensure(table, "first_column");
        ///             //Ensuring the second column
        ///             RefColumn.Ensure(table, "second_column");
        /// 
        ///             foreach(var column in table.Columns)
        ///                 Console.WriteLine("Table column: name = {0}, type = {1}", column.Name, column.ColumnType);
        ///         }
        ///     }
        /// </code>
        /// </example>
        public static RefColumn Ensure(Table table, string columnName)
        {
            if (table == null)
                throw new ArgumentNullException("table");

            return table.EnsureColumn(columnName, ColumnType.Reference) as RefColumn;
        }


        /// <summary>
        ///   Ensures that the table contains a reference column with the specified id and the specified name.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="columnId">The column id.</param>
        /// <param name="columnName">The column name.</param>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="table"/> is null.</exception>
        /// <exception cref="System.Data.Common.DbException">Thrown when trying to add a column and
        /// the current user has no permission to change the schema.</exception>
        /// <exception cref="ArgumentNullException">Thrown when the value of <paramref name="columnName"/> is null.</exception>
        /// <exception cref="ArgumentException">Thrown when the value of <paramref name="columnName"/> is an empty string.</exception>
        /// <exception cref="ArgumentException">Thrown when the value of <paramref name="columnName"/> is not a valid identifier.</exception>
        /// <exception cref="DbOperationException">Thrown when the table already has a column with the
        /// specified id but different column name.</exception>
        /// <exception cref="DbOperationException">Thrown when the table already has a column with the 
        /// specified id but different column type.</exception>
        /// <exception cref="DbOperationException">Thrown when the table already has a column with the specified name
        /// but different column id.</exception>
        /// <returns>Returns the table column with the specified id, the specified name and the specified type.</returns>
        /// <returns>Returns the table column with the specified id, the specified name and the specified type.</returns>
        /// <example>
        ///   The following example shows how to ensure a reference column with the specified id and
        /// the specified name.
        /// <code>
        ///     using System;
        ///     using Centido.Core;
        /// 
        ///     class Sample
        ///     {
        ///         static void Main()
        ///         {
        ///             var domainConfig = new DomainConfiguration()
        ///             {
        ///                 DbConnectionString = @"Driver={SQL Server};Server=.\SQLEXPRESS;Database=MyDb;Uid=admin;Pwd=P@ssw0rd;",
        ///                 DbProviderName = "MSSQLSERVER"
        ///             };
        ///         
        ///             var domain = Domain.Build(domainConfig);
        ///             
        ///             domain.DeleteAll();
        ///             
        ///             //Creating a new table
        ///             var table = domain.AddTable("my_table");
        /// 
        ///             //Creating a table column
        ///             var columnId = Guid.NewGuid();
        ///             table.AddColumn(columnId, "first_column", ColumnType.Reference);
        /// 
        ///             //Ensuring the first column
        ///             RefColumn.Ensure(table, columnId, "first_column");
        ///             //Ensuring the second column
        ///             RefColumn.Ensure(table, Guid.NewGuid(), "second_column");
        /// 
        ///             foreach(var column in table.Columns)
        ///                 Console.WriteLine("Table column: name = {0}, type = {1}", column.Name, column.ColumnType);
        ///         }
        ///     }
        /// </code>
        /// </example>
        public static RefColumn Ensure(Table table, Guid columnId, string columnName)
        {
            if (table == null)
                throw new ArgumentNullException("table");

            return table.EnsureColumn(columnId, columnName, ColumnType.Reference) as RefColumn;
        }
    }
}